﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Client;
using System.Windows.Forms;
using System.Data;
using Microsoft.SharePoint;
using System.Collections;

namespace SPMigrationOracleUtility
{
    class mapping_calss
    {
        DataTable dt;

        mapping_posts objMapPost = new mapping_posts();
        Map_Col_List objColmColl = new Map_Col_List();
        // Mapping_List objlist = new Mapping_List();
        public string mapname { set; get; }
        public string mapdesc { set; get; }
        public string allitems { set; get; }
        public string query { set; get; }
        public string sqlquery { set; get; }
        public string viewname { set; get; }
        public string webUrl { get; set; }
        public string srcType { get; set; }
        public string destType { get; set; }
        public string SiteUrl { get; set; }
        public int lstTemCnt { get; set; }
        public int libTemCnt { get; set; }
        public string tblname { set; get; }
       
        // for Excel data migration
        public string ExcelSheet { set; get; }
        public string ExcelfilePath { set; get; }

        
        public string mainpath;
        ComboBox cboListLib = new ComboBox();

        public void mapping_lists(DataGridView gv, string url)
        {

            DataRow dr;
            DataTable dt = new DataTable();
            dt.Columns.Add("MappingName", typeof(string));
            dt.Columns.Add("MappingDescription", typeof(string));
            dt.Columns.Add("AllItems", typeof(string));
            dt.Columns.Add("ListQuery", typeof(string));
            dt.Columns.Add("ViewName", typeof(string));
            dt.Columns.Add("MappingId", typeof(string));


            using (ClientContext cliContext = new ClientContext(url))
            {
                Web web = cliContext.Web;
                List list = web.Lists.GetByTitle("Mapping List");
                CamlQuery cquery = CamlQuery.CreateAllItemsQuery();
                ListItemCollection listItems = list.GetItems(cquery);
                cliContext.Load(listItems);
                cliContext.ExecuteQuery();
                cliContext.Load(list);
                cliContext.ExecuteQuery();

                //SharePoint Mapping List Posted To mapping_posts class
                objMapPost.Map_List_Posted(listItems);

                foreach (ListItem li in listItems)
                {
                    dr = dt.NewRow();
                    dr["MappingName"] = li["Title"];
                    string desc = Convert.ToString(li["Description"]);
                    dr["MappingDescription"] = HtmlRemoval.StripTagsRegex(desc);
                    dr["AllItems"] = li["All_Items"];
                    string qry = Convert.ToString(li["Query"]);
                    query = HtmlRemoval.StripTagsRegex(qry);
                    dr["ListQuery"] = HtmlRemoval.StripTagsRegex(qry);
                    dr["ViewName"] = li["View_Name"];
                    dr["MappingId"] = li["ID"];
                    dt.Rows.Add(dr);

                }

                gv.DataSource = dt;


            }

        }

        // for Excel Mapping
        public void mapping_lists_Excel(DataGridView gv, string url,string migMode)
        {

            DataRow dr;
            DataTable dt = new DataTable();
            dt.Columns.Add("MappingName", typeof(string));
            dt.Columns.Add("MappingDescription", typeof(string));
            dt.Columns.Add("AllItems", typeof(string));
            dt.Columns.Add("ListQuery", typeof(string));
            dt.Columns.Add("ViewName", typeof(string));
            dt.Columns.Add("MappingId", typeof(string));

            using (ClientContext cliContext = new ClientContext(url))
            {
                Web web = cliContext.Web;
                List list = web.Lists.GetByTitle("Mapping List");
                CamlQuery cquery = new CamlQuery();
                cquery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='MappingMode' /><Value Type='Choice'>" + migMode + "</Value></Eq></Where></Query></View>";
                ListItemCollection listItems = list.GetItems(cquery);
                cliContext.Load(listItems);
                cliContext.ExecuteQuery();
                cliContext.Load(list);
                cliContext.ExecuteQuery();

                //SharePoint Mapping List Posted To mapping_posts class
                //objMapPost.Map_List_Posted(listItems);

                foreach (ListItem li in listItems)
                {
                    dr = dt.NewRow();
                    dr["MappingName"] = li["Title"];
                    string desc = Convert.ToString(li["Description"]);
                    dr["MappingDescription"] = HtmlRemoval.StripTagsRegex(desc);
                    dr["AllItems"] = li["All_Items"];
                    string qry = Convert.ToString(li["Query"]);
                    query = HtmlRemoval.StripTagsRegex(qry);
                    dr["ListQuery"] = HtmlRemoval.StripTagsRegex(qry);
                    dr["ViewName"] = li["View_Name"];
                    dr["MappingId"] = li["ID"];
                    dt.Rows.Add(dr);
                }

                gv.DataSource = dt;
            }

        }

        public DataTable mapping_coll_name_bind(DataGridView gvp, string url, string mapId)
        {

            DataRow dr;
            DataTable dt = new DataTable();
            dt.Columns.Add("SourceTableName", typeof(string));
            dt.Columns.Add("SourceTableColumn", typeof(string));
            dt.Columns.Add("SourceColumnType", typeof(string));
            dt.Columns.Add("DestinationListName", typeof(string));
            dt.Columns.Add("DestinationListColumn", typeof(string));
            dt.Columns.Add("DestinationColumnType", typeof(string));
            dt.Columns.Add("DestinationListTemplate", typeof(string));

            using (ClientContext cliContext = new ClientContext(url))
            {
                Web web = cliContext.Web;
                List list = web.Lists.GetByTitle("Mapping column List");
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapId + "</Value></Eq></Where></Query></View>";
                ListItemCollection listItems = list.GetItems(camlQuery);
                cliContext.Load(listItems);
                cliContext.ExecuteQuery();
                cliContext.Load(list);
                cliContext.ExecuteQuery();

                //SharePoint Mapping List Posted To mapping_posts class
                // objMapPost.Map_List_Posted(listItems);

                foreach (ListItem li in listItems)
                {
                    dr = dt.NewRow();
                    dr["SourceTableName"] = li["Source_Table_Name"];
                    //string desc = Convert.ToString(li["Description"]);
                    dr["SourceTableColumn"] = li["Source_Field"];
                    dr["SourceColumnType"] = li["Source_Field_Type"];

                    //string qry = Convert.ToString(li["Query"]);
                    dr["DestinationListName"] = li["Destination_List_Name"];
                    dr["DestinationListColumn"] = li["Destination_Field"];
                    dr["DestinationColumnType"] = li["Destination_Field_Type"];
                    dr["DestinationListTemplate"] = li["Destination_List_Template"];
                    dt.Rows.Add(dr);

                }
                gvp.DataSource = dt;
            }
            return dt;
        }

        public void Mapping_List_Retrieve(string mapId)
        {

            SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);

            try
            {
                ClientContext clientContext = new ClientContext(SiteUrl);
                List oList = clientContext.Web.Lists.GetByTitle("Mapping List");
                clientContext.Load(oList);
                clientContext.ExecuteQuery();
                CamlQuery camlQuery = new CamlQuery();

                camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>" + mapId + "</Value></Eq></Where></Query></View>";
                ListItemCollection collListItem = oList.GetItems(camlQuery);
                clientContext.Load(collListItem);
                clientContext.ExecuteQuery();
                foreach (ListItem item in collListItem)
                {
                    mapname = Convert.ToString(item["Title"]);
                    string desc = Convert.ToString(item["Description"]);
                    mapdesc = HtmlRemoval.StripTagsRegex(desc);
                    allitems = Convert.ToString(item["All_Items"]);
                    query = Convert.ToString(item["Query"]);
                    
                    sqlquery = HtmlRemoval.StripTagsRegex(query);
                    sqlquery = HtmlRemoval.StripTagsRegex1(sqlquery);
                    viewname = Convert.ToString(item["View_Name"]);

                    ExcelfilePath = Convert.ToString(item["ExcelFilePath"]);
                    ExcelfilePath = HtmlRemoval.StripTagsRegex(ExcelfilePath);
                    clientContext.Load(item);
                    clientContext.ExecuteQuery();
                    new MappingListValues { MappingName = Convert.ToString(item["Title"]), Description = desc, AllItems = Convert.ToString(item["All_Items"]), Query = query, viewName = Convert.ToString(item["View_Name"]), Itemid = Convert.ToInt32(item["ID"]), filePath= ExcelfilePath };

                }


            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }

        public List RetrieveList(string url, string ListName)
        {
            List olist = null;
            try
            {

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);              
            }
            return olist;
        }

        public void Mapping_Col_List_Retrieve(string mapId, ListBox l1, ListBox l2)
        {
            SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
            lstTemCnt = 0;
            libTemCnt = 0;
            try
            {
                ClientContext cltContext = new ClientContext(SiteUrl);
                List dtList = cltContext.Web.Lists.GetByTitle("Mapping Column List");
                cltContext.Load(dtList);
                cltContext.ExecuteQuery();
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapId + "</Value></Eq></Where></Query></View>";
                ListItemCollection collDetails = dtList.GetItems(camlQuery);
                cltContext.Load(collDetails);
                cltContext.ExecuteQuery();


                foreach (ListItem item in collDetails)
                {

                    objColmColl.srcTblName = Convert.ToString(item["Source_Table_Name"]);

                    objColmColl.destListName = Convert.ToString(item["Destination_List_Name"]);

                    objColmColl.srcField = Convert.ToString(item["Source_Field"]);
                    objColmColl.destField = Convert.ToString(item["Destination_Field"]);
                    objColmColl.srcType = Convert.ToString(item["Source_Field_Type"]);
                    objColmColl.destType = Convert.ToString(item["Destination_Field_Type"]);

                    objColmColl.dest_Template = Convert.ToString(item["Destination_List_Template"]);
                    FieldUrlValue objUrl = (FieldUrlValue)(item["Web_Url"]);
                    webUrl = objUrl.Url;
                    List olist = RetrieveList(webUrl, Convert.ToString(objColmColl.destListName));

                    new Map_Col_List
                    {
                        srcTblName = Convert.ToString(item["Source_Table_Name"]),
                        destListName = Convert.ToString(item["Destination_List_Name"]),
                        srcField = Convert.ToString(item["Source_Field"]),
                        srcType = Convert.ToString(item["Source_Field_Type"]),
                        destField = Convert.ToString(item["Destination_Field"]),
                        destType = Convert.ToString(item["Destination_Field_Type"]),
                        dest_Template = Convert.ToString(item["Destination_List_Template"])
                    };





                    if (objColmColl.dest_Template == "DocumentLibrary")
                    {
                        libTemCnt = libTemCnt + 1;
                    }
                    else if (objColmColl.dest_Template == "GenericList")
                    {
                        lstTemCnt = lstTemCnt + 1;
                    }

                    else
                    {

                    }


                    if (l1.Items.Count == 0)
                    {
                        l1.Items.Add(objColmColl.srcTblName);

                    }
                    for (int i = 0; i < l1.Items.Count; i++)
                    {
                        if (!string.Equals(Convert.ToString(objColmColl.srcTblName), Convert.ToString(l1.Items[i])))
                        {
                            l1.Items.Add(objColmColl.srcTblName);
                        }
                    }

                    if (l2.Items.Count == 0)
                    {
                        //  ListItemPair test = new ListItemPair() { Text = objColmColl.destListName, ListTemplate = objColmColl.destListName};

                        l2.Items.Add(objColmColl.destListName);

                    }
                    for (int i = 0; i < l2.Items.Count; i++)
                    {
                        if (!string.Equals(Convert.ToString(objColmColl.destListName), Convert.ToString(l2.Items[i])))
                        {
                            l2.Items.Add(objColmColl.destListName);
                        }
                    }



                    srcType = Convert.ToString(item["Source_Field_Type"]);
                    destType = Convert.ToString(item["Destination_Field_Type"]);
                    cltContext.Load(item);
                    cltContext.ExecuteQuery();
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }

        public void GetListFields(ComboBox cmbList, ComboBox cmbListFields, string url)
        {
            cmbListFields.Items.Clear();
            cmbListFields.DisplayMember = "displayName";
            try
            {
                using (ClientContext clientContext = new ClientContext(url))
                {
                    Web oweb = clientContext.Web;
                    //ListItemPair listName = (ListItemPair)cmbList.SelectedItem;
                    string listName = Convert.ToString(cmbList.SelectedItem);
                    List lst = oweb.Lists.GetByTitle(listName);
                    FieldCollection fldCollec = lst.Fields;
                    clientContext.Load(fldCollec);
                    clientContext.ExecuteQuery();
                    foreach (Field fld in fldCollec)
                    {
                        if (fld.InternalName == "Title" || fld.InternalName == "FileLeafRef")
                        {
                            cmbListFields.Items.Add(new DestFieldType { displayName = fld.Title, internalName = fld.InternalName, fieldType = fld.TypeAsString });
                        }
                        else if (!fld.ReadOnlyField && !fld.Hidden)
                        {
                            if (!fld.FromBaseType)
                                cmbListFields.Items.Add(new DestFieldType { displayName = fld.Title, internalName = fld.InternalName, fieldType = fld.TypeAsString });
                        }

                    }
                    if (cmbListFields.Items.Count > 0)
                        cmbListFields.SelectedIndex = 0;
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Invalid URL");
            }
        }

        public void GetSubSites(ComboBox cmbbox, string url)
        {
            mainpath = url;
            try
            {
                //this.ocmbbx = cmbbox;
                List<ListItemPair> data = new List<ListItemPair>();
                using (ClientContext cliContext = new ClientContext(mainpath))
                {
                    Web web = cliContext.Web;
                    cliContext.Load(web);
                    cliContext.ExecuteQuery();
                    cmbbox.Items.Add(new ListItemPair() { Value = Convert.ToString(web.Id), Text = web.Title, URL = url });
                }
                cmbbox.DisplayMember = "Text";
                getSubWebs(mainpath);

            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);              
            }
        }

        public void getSubWebs(string path)
        {
            try
            {
                ClientContext clientContext = new ClientContext(path);
                Web oWebsite = clientContext.Web;
                clientContext.Load(oWebsite, website => website.Webs, website => website.Title);
                clientContext.ExecuteQuery();

                foreach (Web orWebsite in oWebsite.Webs)
                {
                    string newpath = mainpath + orWebsite.ServerRelativeUrl;
                    cboListLib.Items.Add(new ListItemPair() { Value = Convert.ToString(orWebsite.Id), Text = Convert.ToString(orWebsite.Title), URL = Convert.ToString(newpath) });
                    getSubWebs(newpath);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        public void GetLists_Items(ListBox lstBox, ComboBox lstSubSite, string url)
        {
            mainpath = url;
            try
            {

                ListItemPair subsite = (ListItemPair)lstSubSite.SelectedItem;
                List<ListItemPair> data = new List<ListItemPair>();
                using (ClientContext clientContext = new ClientContext(mainpath))
                {
                    Web site = clientContext.Web;
                    clientContext.Load(site);
                    clientContext.ExecuteQuery();
                    if (subsite.Text == site.Title)
                    {
                        Web oweb = clientContext.Web;
                        ListCollection listColl = oweb.Lists;
                        clientContext.Load(listColl);
                        clientContext.ExecuteQuery();
                        foreach (List olist in listColl)
                        {
                            lstBox.Items.Add(new ListItemPair() { Value = Convert.ToString(olist.Id), Text = olist.Title, ListTemplate = olist.BaseType, URL = subsite.URL });
                        }
                    }
                }
                lstBox.DisplayMember = "Text";
                //url = siteUrl;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void GetUrl(string mapId)
        {
            SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
            //lstTemCnt = 0;
            // libTemCnt = 0;
            try
            {
                ClientContext cltContext = new ClientContext(SiteUrl);
                List dtList = cltContext.Web.Lists.GetByTitle("Mapping Column List");
                cltContext.Load(dtList);
                cltContext.ExecuteQuery();
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapId + "</Value></Eq></Where></Query></View>";
                ListItemCollection collDetails = dtList.GetItems(camlQuery);
                cltContext.Load(collDetails);
                cltContext.ExecuteQuery();

                foreach (ListItem item in collDetails)
                {
                    FieldUrlValue objUrl = (FieldUrlValue)item["Web_Url"];
                    webUrl = objUrl.Url;
                    tblname = Convert.ToString(item["Source_Table_Name"]);
                    //List olist = RetrieveList(webUrl, Convert.ToString(objColmColl.destListName));
                }


            }
            catch (SPException ee)
            {
                MessageBox.Show(ee.Message);
            }

        }

        public void Mapping_Col_List_Edit(string mapId, ListBox lstDBRec, ListBox lstDBSel, ListBox lstSPRec, ListBox lstSPSel)
        {
            SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
            lstTemCnt = 0;
            libTemCnt = 0;
            try
            {
                ClientContext cltContext = new ClientContext(SiteUrl);
                List dtList = cltContext.Web.Lists.GetByTitle("Mapping Column List");
                cltContext.Load(dtList);
                cltContext.ExecuteQuery();
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapId + "</Value></Eq></Where></Query></View>";
                ListItemCollection collDetails = dtList.GetItems(camlQuery);
                cltContext.Load(collDetails);
                cltContext.ExecuteQuery();


                foreach (ListItem item in collDetails)
                {

                    objColmColl.srcTblName = Convert.ToString(item["Source_Table_Name"]);
                    objColmColl.destListName = Convert.ToString(item["Destination_List_Name"]);
                    objColmColl.srcField = Convert.ToString(item["Source_Field"]);
                    objColmColl.destField = Convert.ToString(item["Destination_Field"]);
                    objColmColl.srcType = Convert.ToString(item["Source_Field_Type"]);
                    objColmColl.destType = Convert.ToString(item["Destination_Field_Type"]);

                    objColmColl.dest_Template = Convert.ToString(item["Destination_List_Template"]);
                    FieldUrlValue objUrl = (FieldUrlValue)(item["Web_Url"]);
                    webUrl = objUrl.Url;
                    List olist = RetrieveList(webUrl, Convert.ToString(objColmColl.destListName));

                    for (int i = 0; i < lstSPRec.Items.Count; i++)
                    {
                        ListItemPair li = (ListItemPair)lstSPRec.Items[i];
                        if (li.Text == Convert.ToString(item["Destination_List_Name"]))
                        {
                            // lstSPSel.Items.Add(item["Destination_List_Name"]);
                            lstSPSel.Items.Add(lstSPRec.Items[i]);
                            lstSPRec.Items.Remove(lstSPRec.Items[i]);
                            List_Lib_Cnt();
                        }

                        else
                        {

                        }
                    }

                    for (int i = 0; i < lstDBRec.Items.Count; i++)
                    {

                        if (string.Equals(lstDBRec.Items[i], item["Source_Table_Name"]))
                        {
                            lstDBSel.Items.Add(lstDBRec.Items[i]);
                            lstDBRec.Items.Remove(lstDBRec.Items[i]);

                        }
                        else
                        {

                        }

                    }

                    srcType = Convert.ToString(item["Source_Field_Type"]);
                    destType = Convert.ToString(item["Destination_Field_Type"]);
                    cltContext.Load(item);
                    cltContext.ExecuteQuery();
                }

            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }

        public void List_Lib_Cnt()
        {
            if (objColmColl.dest_Template == "DocumentLibrary")
            {
                libTemCnt = libTemCnt + 1;
            }
            else if (objColmColl.dest_Template == "GenericList")
            {
                lstTemCnt = lstTemCnt + 1;
            }

            else
            {

            }
        }


        public void Mapping_List(string name, string desc, string allitems, string query, string view, string mapid,string filePath)
        {
            try
            {
                using (var clientContext = new ClientContext(SiteUrl))
                {
                    var list = clientContext.Web.Lists.GetByTitle("Mapping List");
                    var recToModify = list.GetItemById(mapid);
                    recToModify["Title"] = name;
                    recToModify["Description"] = desc;
                    recToModify["All_Items"] = allitems;
                    recToModify["Query"] = query;
                    recToModify["View_Name"] = view;
                    if (string.IsNullOrEmpty(filePath))
                    {
                        recToModify["MappingMode"] = "Oracle Migration";
                    }
                    else
                    {
                        recToModify["MappingMode"] = "Excel Migration";
                    }
                    recToModify["ExcelFilePath"] = filePath;
                    recToModify.Update();
                    clientContext.ExecuteQuery();
                }
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }

        public void Mapping_Column_List(DataTable dtTable, string url,string mapid)
        {
            //SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
            try
            {
                //using (var clientContext = new ClientContext(url))
                //    {
                //        var list = clientContext.Web.Lists.GetByTitle("Mapping column List");
                //        var product = list.GetItemById(mapid);
                //        product.DeleteObject();
                //        clientContext.ExecuteQuery();
                //    }

                using (ClientContext clientContext = new ClientContext(url))
                {
                    Web oWebsite = clientContext.Web;
                    List oList = oWebsite.Lists.GetByTitle("Mapping column List");
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapid + "</Value></Eq></Where></Query></View>";
                    ListItemCollection collDetails = oList.GetItems(camlQuery);
                    clientContext.Load(collDetails);
                    clientContext.ExecuteQuery();
                    ArrayList idArr = new ArrayList();
                    foreach (ListItem item in collDetails)
                    {
                        idArr.Add(item["ID"]);
                       // clientContext.ExecuteQuery();
                    }

                    foreach (int val in idArr)
                    {
                        using (var clictx = new ClientContext(url))
                        {
                            var list = clictx.Web.Lists.GetByTitle("Mapping Column List");
                            var product = list.GetItemById(val);
                            product.DeleteObject();
                            clictx.ExecuteQuery();
                        }
                    }


                    using (ClientContext cliContext = new ClientContext(url))
                    {
                        Web web = cliContext.Web;
                        List list = web.Lists.GetByTitle("Mapping Column List");
                        cliContext.Load(list);
                        cliContext.ExecuteQuery();
                        foreach (DataRow dtRow in dtTable.Rows)
                        {
                            ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                            ListItem item = list.AddItem(itemCreateInfo);

                            item["Mapping_ID"] = mapid;
                            item["Source_Table_Name"] = dtRow["SourceTableName"];
                            item["Destination_List_Name"] = dtRow["DestinationListName"];
                            item["Source_Field"] = dtRow["SourceTableColumn"];
                            item["Destination_Field"] = dtRow["DestinationListColumn"];
                            item["Source_Field_Type"] = dtRow["SourceColumnType"];
                            item["Destination_Field_Type"] = dtRow["DestinationColumnType"];
                            item["Web_Url"] = webUrl;
                            item["Destination_List_Template"] = dtRow["DestinationListTemplate"];
                            item.Update();
                            cliContext.ExecuteQuery();
                        }
                    }

                }  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        public void Mapping_Delete(string mapid)
        {
            SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);

            try
            {
                using (ClientContext clientContext = new ClientContext(SiteUrl))
                {
                    Web oWebsite = clientContext.Web;
                    List oList = oWebsite.Lists.GetByTitle("Mapping List");
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Lookup'>" + mapid + "</Value></Eq></Where></Query></View>";
                    ListItemCollection collDetails = oList.GetItems(camlQuery);
                    clientContext.Load(collDetails);
                    clientContext.ExecuteQuery();
                    ArrayList idArr = new ArrayList();

                    foreach (ListItem li in collDetails)
                    {
                        idArr.Add(li["ID"]);
                    }
                     
                   
                    foreach (int val in idArr)
                    {
                        using (var clictx = new ClientContext(SiteUrl))
                        {
                            var list = clictx.Web.Lists.GetByTitle("Mapping List");
                            var product = list.GetItemById(val);
                            product.DeleteObject();
                            clictx.ExecuteQuery();
                        }
                    }


                }
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
            
            try
            {

                using (ClientContext clientContext = new ClientContext(SiteUrl))
                {
                    Web oWebsite = clientContext.Web;
                    List oList = oWebsite.Lists.GetByTitle("Mapping column List");
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapid + "</Value></Eq></Where></Query></View>";
                    ListItemCollection collDetails = oList.GetItems(camlQuery);
                    clientContext.Load(collDetails);
                    clientContext.ExecuteQuery();
                    ArrayList idArr = new ArrayList();
                    foreach (ListItem item in collDetails)
                    {
                        idArr.Add(item["ID"]);
                        // clientContext.ExecuteQuery();
                    }

                    foreach (int val in idArr)
                    {
                        using (var clictx = new ClientContext(SiteUrl))
                        {
                            var list = clictx.Web.Lists.GetByTitle("Mapping Column List");
                            var product = list.GetItemById(val);
                            product.DeleteObject();
                            clictx.ExecuteQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        //for excel migration
        public void MappingColumn(string mapId, ListBox lstSPRec, ListBox lstSPSel)
        {
            //SiteUrl = Convert.ToString(oset.Properties["SiteURL"].DefaultValue);
             SiteUrl = Convert.ToString(SPMigrationOracleUtility.Appl.Default.SiteURL);
            lstTemCnt = 0;
            libTemCnt = 0;
            try
            {
                ClientContext cltContext = new ClientContext(SiteUrl);
                List dtList = cltContext.Web.Lists.GetByTitle("Mapping Column List");
                cltContext.Load(dtList);
                cltContext.ExecuteQuery();
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Mapping_ID' /><Value Type='Lookup'>" + mapId + "</Value></Eq></Where></Query></View>";
                ListItemCollection collDetails = dtList.GetItems(camlQuery);
                cltContext.Load(collDetails);
                cltContext.ExecuteQuery();


                foreach (ListItem item in collDetails)
                {

                    objColmColl.srcTblName = Convert.ToString(item["Source_Table_Name"]);
                    objColmColl.destListName = Convert.ToString(item["Destination_List_Name"]);
                    objColmColl.srcField = Convert.ToString(item["Source_Field"]);
                    objColmColl.destField = Convert.ToString(item["Destination_Field"]);
                    objColmColl.srcType = Convert.ToString(item["Source_Field_Type"]);
                    objColmColl.destType = Convert.ToString(item["Destination_Field_Type"]);

                    objColmColl.dest_Template = Convert.ToString(item["Destination_List_Template"]);
                    FieldUrlValue objUrl = (FieldUrlValue)(item["Web_Url"]);
                    webUrl = objUrl.Url;
                    List olist = RetrieveList(webUrl, Convert.ToString(objColmColl.destListName));

                    for (int i = 0; i < lstSPRec.Items.Count; i++)
                    {
                        ListItemPair li = (ListItemPair)lstSPRec.Items[i];
                        if (li.Text == Convert.ToString(item["Destination_List_Name"]))
                        {
                            // lstSPSel.Items.Add(item["Destination_List_Name"]);
                            lstSPSel.DisplayMember = "Text";
                            lstSPSel.Items.Add(lstSPRec.Items[i]);
                            lstSPRec.Items.Remove(lstSPRec.Items[i]);
                            List_Lib_Cnt();
                        }

                        else
                        {

                        }
                    }

                    srcType = Convert.ToString(item["Source_Field_Type"]);
                    destType = Convert.ToString(item["Destination_Field_Type"]);

                    cltContext.Load(item);
                    cltContext.ExecuteQuery();
                }

            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }
    }
}

